***********************************************
**************** AUCTIONS DATA ****************
***********************************************
/*
In this do file, we import and prepare the data on
auctions.

*/

*----------------
* PATHS
*----------------
ssc install distinct
ssc inst _gwtmean, replace 
global replication `c(pwd)'
global data "$replication\Data"
global inter "$replication\Dta - Intermediate"
global final "$replication\Dta - Final"
global graphs "$replication\Graphs"

*================
* IMPORTING DATA
*================
clear all
import delimited "$data\Subastas.Summary.0216.ToStata.csv"

*================
* HOUSEKEEPING
*================
* Counting the number of distinct values
replace isin=strlower(isin)
distinct isin
by isin, sort: gen new_isin = _n == 1
label var new_isin "Indicator of New Vintage"
gen isin_indic = sum(new_isin)
label var isin_indic "Cohort of Vintage"
bysort isin: gen isin_freq = _N
label var isin_freq "Count of Isin #"

* Date variables:
	* Vintage date
	gen vintage_date=date(fechaemisióndelprimertramo, "MDY")
	format vintage_date %td
	label var vintage_date "Vintage"
	hist vintage_date

	* Expiration date
	gen expiration_date=date(fechadeamortización, "MDY")
	format expiration_date %td
	label var expiration_date "Expiration"

	* Issuance date
	gen issuance_date=date(fechadedesembolsodelasubasta,"MDY")
	format issuance_date %td
	label var issuance_date "Issuance"

	* Year of issuance
	gen iss_yr=year(issuance_date)
	label var iss_yr "Year of Issuance"

	* Quarter of issuance
	gen iss_qt=quarter(issuance_date)
	label var iss_qt "Quarter of Issuance"

	* Quarter-Year issuance 
	g iss_qy=qofd(issuance_date)
	label var iss_qy "Issuance Quarter Year"
	format iss_qy %tq

	* Month of issuance
	gen iss_mn=month(issuance_date)
	label var iss_mn "Month of Issuance"

	* Day of issuance
	gen iss_dy=day(issuance_date)
	label var iss_dy "Day of Issuance"

	* Vintage year
	gen vin_yr=year(issuance_date)
	label var vin_yr "Year of Vintage"

	* Vintage month
	gen vin_mn=month(issuance_date)
	label var vin_mn "Month of Vintage"

	* Vintage dat
	gen vin_dy=day(issuance_date)
	label var vin_dy "Day of Vintage"

	* Day of the week 
	gen dyofwk=dow(issuance_date)
	label var dyofwk "Day of the Week"
	label define dyofwk 1 "Monday", modify
	label define dyofwk 2 "Tuesday", modify
	label define dyofwk 3 "Wednesday", modify
	label define dyofwk 4 "Thursday", modify
	label define dyofwk 5 "Friday", modify

	* Month-Year issuance
	g iss_my=mofd(issuance_date)
	label var iss_my "Issuance Month Year"
	format iss_my %tm

* Dropping variables
display as text "simple summary = " 
list fechaemisióndelprimertramo vintage_date if (_n<5) 

* Transforming other variables to numeric
	* Coupon variable
	gen coupon = real(cupón)
	label var coupon "Coupon"
	drop cupón

	* Total competitive nominal
	gen totcompbid   = real(nominalpeticionescompetitivas) 	 			 
	label var totcompbid "Total Competitive Nominal"
	drop nominalpeticionescompetitivas

	* Total non-competitive nominal
	gen totnocompbid = real(nominalpeticionesnocompetitivas)         
	label var totnocompbid "Total Non-Compete Nominal"
	drop nominalpeticionesnocompetitivas

	* Total allocated
	gen totalot = real(nominaladjudicado) 				   			 
	label var totalot "Total Allocated"
	drop nominaladjudicado

	* Total issued
	gen totissued    = real(nominalemitido)  				 			 
	label var totissued "Total Issued"
	drop nominalemitido

	* Marginal assigned
	gen marginalot   = real(nominaladjudicadoalmarginal) 	 			 
	label var marginalot "Marginal Assigned"
	drop nominaladjudicadoalmarginal

	* Marginal price
	gen marginprice  = real(preciomarginal)  				 		 
	label var marginprice "Marginal Price"
	drop preciomarginal

	* Marginal rate
	gen marginrate   = real(tipomarginal)    				 			 
	label var marginrate "Marginal Rate"
	drop tipomarginal

	* Weighted avergae price
	gen wavprice     = real(preciomediomedioponderado) 			 			 
	label var wavprice "Weighted Av. Price"
	drop preciomediomedioponderado

	* Weighted average rate
	gen wavrate      = real(tipomedioponderado) 				 			 
	label var wavrate "Weighted Av. Rate"
	drop tipomedioponderado

	* Marginal price -above cut
	gen marginpriceup= real(primerprecionoaceptado) 			        
	label var marginpriceup "Marginal Price (above cut)"
	drop primerprecionoaceptado

	* Price at margin
	rename nominalsolicitadoaeseprecio priceatmargin 	     	 
	label var priceatmargin "Price at Margin" 

	* Days since first vintage
	gen dayspastvintage=issuance_date-vintage_date
	label var dayspastvintage "Days Since First Vintage"
	
* Type of Issuance (Bond class) 
* Switching from string to categorical
encode nombredelaemision, gen(bondclass) 			
label var bondclass "Bond Class"
label list bondclass
recode bondclass ( 2 4 = 3 )
recode bondclass ( 5 = 4 ) 
recode bondclass ( 1 = 5 ) 
recode bondclass ( 6 = 1 ) 
recode bondclass ( 7 = 2 ) 
recode bondclass (8 = 6)
label define bondclass 1 "LETRAS TESORO (3 Month)", modify
label define bondclass 2 "LETRAS TESORO (6 Month)", modify
label define bondclass 3 "LETRAS TESORO  (1 Year)", modify
label define bondclass 4 "LETRAS TESORO (18 Month)", modify
label define bondclass 5 "BONOS ESTADO", modify
label define bondclass 6 "OBLIGACIONES ESTADO", modify
label define bondclass 7 "", modify
label define bondclass 8 "", modify
label list bondclass

* Dropping variables that are not needed
drop nombredelaemision fechaemisióndelprimertramo fechadeamortización fechadedesembolsodelasubasta

* Integers:
	* Tranch
	gen tranchnum=int(numerodetramo) 
	label var tranchnum "# Tranch"
	gen  roundnum=int(numerodevuelta) 
	* Round
	label var roundnum "# Round"
	drop numerodetramo numerodevuelta
	
* Creating New Variables, *! NOTE: the org prefix is for original data
	* Days to maturity
	gen days2mat=expiration_date-issuance_date
	label var days2mat "Days to Maturity"
	* Original maturity (in days)
	gen orgdaysmat=expiration_date-vintage_date
	label var orgdaysmat "Original Maturity (days)"
	* Months to maturity
	gen mon2mat=days2mat/30
	label var mon2mat "Months to Maturity"
	* Original maturity (in months)
	gen orgmon2mat=orgdaysmat/30
	label var orgmon2mat "Original Maturity (months)"
	* Years to maturity
	gen y2mat=days2mat/365
	label var y2mat "Years to Maturity"
	* Original maturity (in years)
	gen orgy2mat=orgdaysmat/365
	label var orgy2mat "Original Maturity (years)"
	* Marginal to weighted price
	gen mar_wavprice=marginprice/wavprice
	label var mar_wavprice "Marginal to Weighted Price"
	* Marginal minus weighted rate
	gen mar_wavrate=marginrate-wavrate
	label var mar_wavrate "Marginal minus Weighted Rate"
	* Total bids
	gen totbid=totcompbid+totnocompbid
	label var totbid "Total Bids"
	* Issuances over bids
	gen coverage=totissued/totbid
	label var coverage "Issuance over Bids"
	* Allocation to marginal over total allocated
	gen mar_totalot=marginalot/totalot
	label var mar_totalot "Allocation to Marginal (over Total)"
	* Months to maturity (rounded)
	gen rmon2mat=round(mon2mat)
	label var rmon2mat "Months to Maturity (rounded)"

* Constructing time series of average maturity
* if not installed: ssc inst _gwtmean, replace
egen matofmonth=mean(rmon2mat), by(iss_my)
label var matofmonth "Average Maturity of Issuances that Month"
egen wmatofmonth=wtmean(rmon2mat), weight(totalot) by(iss_my)
label var wmatofmonth "Maturity of Month (Value Weighted)"
egen matofyr=mean(rmon2mat), by(iss_yr)
label var matofyr "Average Maturity of Issuances that Year"
egen wmatofyr=wtmean(rmon2mat), weight(totalot) by(iss_yr)
label var wmatofyr "Maturity of Year (Value Weighted)"

* Auction Identifier
by vintage_date, sort: gen auction_bnpid = _n == 1
replace auction_bnpid = sum(auction_bnpid)
label var auction_bnpid "Auction Identifier"

* Relabelling and Reorganizing by security
encode isin, gen(sec_bnpid)
sort sec_bnpid issuance_date
order sec_bnpid, first
label var sec_bnpid "Security (BNP id)"

* Cummulative
egen sum_issuance=sum(totalot/wavprice), by(sec_bnpid)
label var sum_issuance "Security Total Face Value (all issuances)"
bysort sec_bnpid : gen cum_iss = sum(totalot/wavprice)
label var cum_iss "Security Total Face Value (time)"

* Merging data on auctions with Spanish GDP data
gen ym_date=ym(iss_yr,iss_mn)
merge m:m ym_date using "$inter\Spain_NominalGDP.dta"
gen totalot_ngdp=totalot/nom_gdp
label var totalot_ngdp "Total Allotment to Nom GDP"
drop _merge

save "$inter\AuctionsData.dta", replace
